#!/usr/bin/env python
# -*- coding: utf-8 -*-
'''
@author: Lénia Andrade nº 5607
'''

import sqlite3


class Statistics:
           
    
    '''
    This method returns the database information referent to a certain year(chosen by the user)
    '''
    def get_database_information(self, year):

        conn = sqlite3.connect('../Rebides.db')
        c = conn.cursor()
        c.execute('''SELECT * FROM teachers_information_year_{0} '''.format(year))
        database_data = c. fetchall()
        conn.close()
        return database_data     
        
    
    '''
    This method returns a list of establishments availables in a certain year(chosen by the user) 
    '''    
    def get_establishments_year(self, year):
        
        database_data = self.get_database_information(year)
        tmp = []
        
        for l in range(0, len(database_data)):                                                   
            tmp.append(database_data[l][8])
            pass
        
        tmp = set(tmp)
        estab_year = list(tmp) 
        estab_year.sort()                
        return estab_year
    


    '''
    This method returns a list of teachers belonging to a certain establishment with a 
    particular category in a specific year(all chosen by the user) 
    '''
    def get_teachers_cat_estab_year(self, year, estab, cat):

        database_data = self.get_database_information(year)
        teachers_cat_estab_year = []
      
        for l in range(0, len(database_data)):                                                                           
            if(database_data[l][8] == estab):
                if(database_data[l][4] == cat):
                    teachers_cat_estab_year.append(database_data[l][1])
                    pass
                pass
            pass
        
        return teachers_cat_estab_year



    '''
    This method returns a list of teachers belonging to a certain establishment having a 
    designated degree with a particular category in a specific year(all chosen by the user) 
    '''
    def get_teachers_deg_estab_cat_year(self, year, estab, deg, cat):
        
        database_data = self.get_database_information(year)
        teachers_deg_estab_cat_year =[]
        
        for l in range(0, len(database_data)):                                                                           
            if(database_data[l][8] == estab):
                if(database_data[l][2] == deg):
                    if(database_data[l][4] == cat):
                        teachers_deg_estab_cat_year.append(database_data[l][1])
                        pass
                    pass
                pass
            pass
        
        return teachers_deg_estab_cat_year 
    
    
       
    
    '''
    This method returns a list of the existing categories in a particular establishment in a specific year  
    '''
    def cat_estab_year(self, year, estab): 
        
        database_data = self.get_database_information(year)
        tmp = []
        for l in range(0, len(database_data)):                                                                           
            if(database_data[l][8] == estab):
                tmp.append(database_data[l][4])
                pass
            pass
        tmp = set(tmp)
        categories = list(tmp)
        categories.sort()
        return categories                 
    
    
    
    '''
    This method returns a list of the existing degrees(doctorate level, masters levels and graduate level) for a certain 
    category in a particular establishment in a specific year  
    '''
    def deg_cat_estab_year(self, year, estab,cat): 
        
        database_data = self.get_database_information(year)
        tmp = []
        for l in range(0, len(database_data)):                                                                           
            if(database_data[l][8] == estab):
                if (database_data[l][4] == cat):
                    tmp.append(database_data[l][2])
                    pass
                pass
            pass

        degrees = (filter(lambda (x): x.startswith('Do'), tmp))
        degrees.extend(filter(lambda (x): x.startswith('L'), tmp))
        degrees.extend(filter(lambda (x): x.startswith('M'), tmp))
                
        degrees = set(degrees)
        degreesList = list(degrees)
        degreesList.sort()
        return degreesList  
    pass